package mwa.cs.dao;

import java.util.List;
import java.util.Map;

import mwa.cs.database.Database;
import mwa.cs.model.CustMonthly;

/**
 * A class used for managing cust_monthly
 */
public class CustMonthlyDAO {

	/** a database of this cust_monthly */
    private Database db;
    
    /**
     * Instantiate a new object of this class
     * @param database a database
     */
    public CustMonthlyDAO(Database db) {
        this.db = db;
    }
    
    /**
     * a method for searching all cust_monthly in database
     * @return all cust_monthly
     */
    public List<Map<String, Object>> findAll() {
        String vaSql = "SELECT * FROM cust_monthly";
        List<Map<String, Object>> result = db.queryList(vaSql);
        if (result != null) {
            return result;
        } else {
            return null;
        }
    }
    
    /**
     * insert into cust_monthly
     * @param poCustMonthly cust_monthly that will insert
     */
    public void insertCustMonthly(CustMonthly poCustMonthly) {
    	String vaSql = "INSERT INTO cust_monthly" + " (site_id, zone_id, route_id, account, period_month, period_year, "
    			+ "seq, customer_class, bill_number, meter_number, meter_read_number, read_date, consumption, "
    			+ "average_consumption, allowance, water_fee, water_charge, vat_amount, bill_amount, comment_code, "
    			+ "status, insert_user_id, insert_date, insert_time, update_user_id, update_date, update_time) " 
        		+ "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        try {
            db.insert(vaSql, poCustMonthly.getVnSiteId(),
            		poCustMonthly.getVnZoneId(),
            		poCustMonthly.getVnRouteId(),
            		poCustMonthly.getVaAccount(),
            		poCustMonthly.getVaPeriodMonth(),
            		poCustMonthly.getVaPeriodYear(),
            		poCustMonthly.getVnSeq(),
            		poCustMonthly.getVaCustomerClass(),
            		poCustMonthly.getVaBillNumber(),
            		poCustMonthly.getVaMeterNumber(),
            		poCustMonthly.getVaMeterReadNumber(),
            		poCustMonthly.getVaReadDate(),
            		poCustMonthly.getVnConsumption(),
            		poCustMonthly.getVnAverageConsumption(),
            		poCustMonthly.getVaAllowance(),
            		poCustMonthly.getVnWaterFee(),
            		poCustMonthly.getVnWaterCharge(),
            		poCustMonthly.getVnVatAmount(),
            		poCustMonthly.getVnBillAmount(),
            		poCustMonthly.getVaCommentCode(),
            		poCustMonthly.getVaStatus(),
            		poCustMonthly.getVnInsertUserId(),
            		poCustMonthly.getVaInsertDate(),
            		poCustMonthly.getvaInsertTime(),
            		poCustMonthly.getVnUpdateUserId(),
            		poCustMonthly.getVaUpdateDate(),
            		poCustMonthly.getVaUpdateTime());
        } catch (Exception e) {
        	e.printStackTrace();
        	throw new RuntimeException(e);
        }
    }
    
    /**
     * a method for updating a cust_monthly into database
     * @param pnSiteId site that customer live
     * @param paAccount that will update
     * @param poNewCustMonthly that will update
     */
    public void updateCustMonthly(int pnSiteId, String paAccount, CustMonthly poNewCustMonthly, 
    		String paPeriodMonth, String paPeriodYear) {
        String vaSql = "UPDATE cust_monthly SET zone_id=?, "
        		+ "route_id=?, seq=?, customer_class=?, meter_number=?, meter_read_number=?, "
        		+ "read_date=?, allowance=?, status=?, "
        		+ "update_user_id=?, update_date=?, update_time=? "
        		+ "WHERE site_id=? and account=? and period_month=? and period_year=?";
        try {       	
        	db.update(vaSql, poNewCustMonthly.getVnZoneId(),
        			poNewCustMonthly.getVnRouteId(),
        			poNewCustMonthly.getVnSeq(),
        			poNewCustMonthly.getVaCustomerClass(),
        			poNewCustMonthly.getVaMeterNumber(),
        			poNewCustMonthly.getVaMeterReadNumber(),
        			poNewCustMonthly.getVaReadDate(),
        			poNewCustMonthly.getVaAllowance(),
        			poNewCustMonthly.getVaStatus(),
        			poNewCustMonthly.getVnUpdateUserId(),
        			poNewCustMonthly.getVaUpdateDate(),
        			poNewCustMonthly.getVaUpdateTime(),
        			pnSiteId,
        			paAccount,
        			paPeriodMonth,
        			paPeriodYear);
        } catch (Exception e) {
        	throw new RuntimeException(e);
        }
    }
    
    /**
     * delete cust_monthly by site_id, account
     * @param pnSiteId is an site_id that want to delete data
     * @param paAccount is an account that want to delete data
     * @return true if success, false if fail
     */
    public boolean deleteCustMonthlyByAccount(int pnSiteId, String paAccount) {
        String vaSql = "DELETE FROM cust_monthly WHERE site_id = ? and account = ?";
        try {
            db.update(vaSql, pnSiteId, paAccount);
            return true;
        } catch (Exception e) {
            return false;
        }
    }
    
    /**
     * finding max month by site_id
     * @param pnSiteId site_id that will find max month
     * @return max month
     */
    public String findMaxMonth(int pnSiteId) {
    	String vaSql = "SELECT MAX(period_month) AS period_month FROM cust_monthly "
    			+ "WHERE period_year = (SELECT MAX(period_year) FROM cust_monthly) and site_id = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
        if (result.get("PERIOD_MONTH") != null) {
            return result.get("PERIOD_MONTH").toString();
        } else {
            return "00";
        }
    }
    
    /**
     * finding max year by site_id
     * @param pnSiteId site_id that will find max year
     * @return max year
     */
    public String findMaxYear(int pnSiteId) {
    	String vaSql = "SELECT MAX(period_year) AS period_year FROM cust_monthly WHERE site_id = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
        if (result.get("PERIOD_YEAR") != null) {
            return result.get("PERIOD_YEAR").toString();
        } else {
            return "0000";
        }
    }
    
    /**
     * finding all cust_monthly in last year for calculate consumption
     * @param pnSiteId site_id that will find cust_monthly
     * @param paAccount account that will find cust_monthly
     * @return all cust_monthly in last year
     */
	public List<Map<String, Object>> findMaxYearData(int pnSiteId, String paAccount, String paMaxMonth) {	
        String vaSql = "SELECT * FROM cust_monthly WHERE period_month not in ? AND account = ? AND site_id = ? AND "
        		+ "period_year = (SELECT MAX(period_year) FROM CUST_MONTHLY) ORDER BY period_month DESC";
        List<Map<String, Object>> result = db.queryList(vaSql, paMaxMonth, paAccount, pnSiteId);
        if (result != null && result.size() != 0) {
            return result;
        } else {
            return null;
        }
	}   
	
	/**
	 * finding all cust_monthly in last year - 1 for calculate consumption
	 * @param pnSiteId site_id that will find cust_monthly
	 * @param paAccount account that will find cust_monthly
	 * @return all cust_monthly in last year - 1
	 */
	public List<Map<String, Object>> findPreviousMaxYearData(int pnSiteId, String paAccount, String paMaxMonth) {
        String vaSql = "SELECT * FROM cust_monthly WHERE period_month not in ? AND account = ? AND site_id = ? AND "
        		+ "period_year = (SELECT MAX((CAST(PERIOD_YEAR AS INTEGER)-1)) FROM CUST_MONTHLY)  ORDER BY period_month DESC";
        List<Map<String, Object>> result = db.queryList(vaSql, paMaxMonth, paAccount, pnSiteId);
        if (result != null && result.size() != 0) {
            return result;
        } else {
            return null;
        }
	}
	
	// แก้ไขข้อมูล AR การอ่านมาตร
  	public boolean updateMeterCustMonthly(CustMonthly poNewCustMonthly) {
          String vaSql = "UPDATE cust_monthly SET "
        		  + "meter_read_number = ?, read_date = ?, consumption = ?, average_consumption = ?, water_fee = ?, "
        		  + "water_charge = ?, vat_amount = ?, bill_amount = ?, comment_code = ?, "
        		  + "update_user_id = ?, update_date = ?, update_time = ? "
        		  + "WHERE site_id = ? AND account = ? AND period_month = ? AND period_year = ?";
        
          boolean result = false;
          try {
          	db.update(vaSql,
          			poNewCustMonthly.getVaMeterReadNumber(),
          			poNewCustMonthly.getVaReadDate(),
          			poNewCustMonthly.getVnConsumption(),
          			poNewCustMonthly.getVnAverageConsumption(),
          			poNewCustMonthly.getVnWaterFee(),
          			poNewCustMonthly.getVnWaterCharge(),
          			poNewCustMonthly.getVnVatAmount(),
          			poNewCustMonthly.getVnBillAmount(),
          			poNewCustMonthly.getVaCommentCode(),
          			poNewCustMonthly.getVnUpdateUserId(),
          			poNewCustMonthly.getVaUpdateDate(),
          			poNewCustMonthly.getVaUpdateTime(),
          			poNewCustMonthly.getVnSiteId(),
          			poNewCustMonthly.getVaAccount(),
          			poNewCustMonthly.getVaPeriodMonth(),
          			poNewCustMonthly.getVaPeriodYear()
          			);
          	result = true;
          } catch (Exception e) {
          	System.out.println(e);
          	result = false;
          }
          return result;
  	}
    
  	// ดึงข้อมูล Comment Code
  	public String getCommentCode(int pnSiteId, String paAccount, String paPeriodMonth, String paPeriodYear) {
    	String vaSql = "SELECT comment_code FROM cust_monthly WHERE site_id = ? AND account = ? AND period_month = ? AND period_year = ?";
    	Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paAccount, paPeriodMonth, paPeriodYear);
    	if (result.get("COMMENT_CODE") != null) {
            return result.get("COMMENT_CODE").toString();
        } else {
            return "";
        }
    }
  	
 // บันทึกข้อมูล CustMonthly ในกรณีที่บันทึกผลการติดตั้งประปา
  	public boolean SaveCustMonthlyForInstall(CustMonthly poNewCustMonthly, String paRequestNumber) {
          String vaSql = "INSERT INTO cust_monthly ( "
        		  + "site_id, zone_id, route_id, account, period_month, period_year, seq, customer_class, meter_number, meter_read_number, read_date, "
        		  + "consumption, average_consumption, allowance, water_fee, water_charge, vat_amount, bill_amount, status, "
        		  + "insert_user_id, insert_date, insert_time) "
        		  + "SELECT ?, ?, ?, account, ?, ?, ?, customer_class, ?, ?, ?, ?, ?, allowance, ?, ?, ?, ?, ?, ?, ?, ? "
        		  + "FROM request WHERE request_number = ?";
        
          boolean result = false;
          try {
          	db.update(vaSql,
          			poNewCustMonthly.getVnSiteId(),
          			poNewCustMonthly.getVnZoneId(),
          			poNewCustMonthly.getVnRouteId(),
          			poNewCustMonthly.getVaPeriodMonth(),
          			poNewCustMonthly.getVaPeriodYear(),
          			poNewCustMonthly.getVnSeq(),
          			poNewCustMonthly.getVaMeterNumber(),
          			poNewCustMonthly.getVaMeterReadNumber(),
          			poNewCustMonthly.getVaReadDate(),
          			poNewCustMonthly.getVnConsumption(),
          			poNewCustMonthly.getVnAverageConsumption(),
          			poNewCustMonthly.getVnWaterFee(),
          			poNewCustMonthly.getVnWaterCharge(),
          			poNewCustMonthly.getVnVatAmount(),
          			poNewCustMonthly.getVnBillAmount(),
          			poNewCustMonthly.getVaStatus(),
          			poNewCustMonthly.getVnInsertUserId(),
          			poNewCustMonthly.getVaInsertDate(),
          			poNewCustMonthly.getvaInsertTime(),
          			paRequestNumber);
          	result = true;
          } catch (Exception e) {
          	System.out.println(e);
          	result = false;
          }
          return result;
  	}
  	
}
